<?php
require_once("../libraries/PHPExcel.php");
class ExcelReader
{
	private $filePath;
	private $objPHPExcel;
	private $objWorksheet;
	private $content;
	public function __construct($filePath)
	{
		$this->filePath = $filePath;
		$this->objPHPExcel=PHPExcel_IOFactory::load($filePath);
		$this->content=array();
	}
	public function read()
	{
		$sheetCount = $this->objPHPExcel->getSheetCount();
		for($i=0;$i<$sheetCount;$i++)
		{
			$sheetContent = array();
			$this->objWorksheet = $this->objPHPExcel->setActiveSheetIndex($i);
			$content = $this->objWorksheet->toArray(true,null,null,null);
			$highestRow = $this->objWorksheet->getHighestRow();
			$highestColString =$this->objWorksheet->getHighestColumn();
			$highestCol=PHPExcel_Cell::columnIndexFromString($highestColString);
			for($row=1;$row <= $highestRow;$row++)
			{
				$rowContent = array();
				for($col=0;$col <$highestCol;$col++)
				{
					$cell = $this->objWorksheet->getCellByColumnAndRow($col,$row);
					$rowContent[count($rowContent)] = $cell->getValue();
				}
				$sheetContent[count($sheetContent)] = $rowContent;
			}
			$this->content[count($this->content)]=$sheetContent;
		}
		return $this->content;
	}
}
class ExcelImport
{
	private $content;
	public function __construct($content)
	{
		$this->content = $content;
		include("../app/connection.php");
	}
	public function import($table,$position)
	{
		////////////Create query header//////////////
		$query = "INSERT INTO ".$table." VALUES (";
//		foreach($fields as $str)
//		{
//			$query=$query.$str.",";
//		}
//		$query = substr($query,0,strlen($query)-1);
//		$query = $query.") VALUES (";
		///////////Separate Sheet////////////
		foreach($this->content as $sheet)
		{
			///////////Separate Row/////////////
			$i=1;
			foreach($sheet as $row)
			{
				/////////Eject Row 1 - Title//////////////
				if($i==1)
				{
					$i=2;
				}
				else
				{
					$subquery = $query;
					for($j=0;$j<count($row);$j++)
					{
						if(in_array($j,$position))
						{
							$subquery=$subquery."'".$row[$j]."',";
						}
						else
						{
							$subquery=$subquery.$row[$j].",";
						}
					}
					$subquery= substr($subquery,0,strlen($subquery)-1);
					$subquery=$subquery.")";
					$result = mysql_query($subquery);
					//echo  $subquery."<br>";
					if($result!=false)echo $subquery." <font color='#0000FF'>(OK) </font><br>";
					else echo $subquery." <font color='#FF0000'> <------------Error----------</font><br>";
				}
			}
		}
	}
	public function importResult($table,$position)
	{
		////////////Create query header//////////////
		$query = "INSERT INTO ".$table." VALUES (";
		$updateQuery = "UPDATE ".$table." SET ";
		$checkQuery = "SELECT COUNT(*) from ".$table." ";
		$whereQuery =" where ";
//		foreach($fields as $str)
//		{
//			$query=$query.$str.",";
//		}
//		$query = substr($query,0,strlen($query)-1);
//		$query = $query.") VALUES (";
		///////////Separate Sheet////////////
		foreach($this->content as $sheet)
		{
			///////////Separate Row/////////////
			$i=1;
			foreach($sheet as $row)
			{
				/////////Eject Row 1 - Title//////////////
				if($i==1)
				{
					$i=2;
				}
				else
				{
					$subquery = $query;
					$subWhereQuery = $whereQuery;
					$subCheckQuery = "";
					$subUpdateQuery="";
					for($j=0;$j<count($row);$j++)
					{
						if($j==0)
						{
							$subWhereQuery = $subWhereQuery."MSSV = ".$row[$j]." and ";
						}
						if($j==1)
						{
							$subWhereQuery = $subWhereQuery."MAMONHOC = '".$row[$j]."' and ";
						}
						if($j==2)
						{
							$subWhereQuery = $subWhereQuery."HOCKY = '".$row[$j]."' and ";
						}
						if($j==3)
						{
							$subWhereQuery = $subWhereQuery."NAM = '".$row[$j]."'";
						}
						if($j==4)
						{
							$subUpdateQuery = "DIEMTHUCHANH = '".$row[$j]."' ".$subUpdateQuery;
						}
						if($j==5)
						{
							$subUpdateQuery = "DIEMGIUAKY = '".$row[$j]."', ".$subUpdateQuery;
						}
						if($j==6)
						{
							$subUpdateQuery = "DIEMCUOIKY = '".$row[$j]."', ".$subUpdateQuery;
						}
						if($j==7)
						{
							$subUpdateQuery = "DIEMTRUNGBINH = '".$row[$j]."', ".$subUpdateQuery;
						}
						
						if(in_array($j,$position))
						{
							$subquery=$subquery."'".$row[$j]."',";
						}
						else
						{
							$subquery=$subquery.$row[$j].",";
						}
					}
					$subCheckQuery=$checkQuery.$subWhereQuery;
					$subUpdateQuery= $updateQuery." ".$subUpdateQuery.$subWhereQuery;
					$subquery= substr($subquery,0,strlen($subquery)-1);
					$subquery=$subquery.")";
					$result = mysql_query($subCheckQuery);
					$result = mysql_fetch_array($result);
					if($result[0]=="0")
					{
						//echo $subquery."<br>";
						$result=mysql_query($subquery);
						if($result!=false)echo $subquery." <font color='#0000FF'>(OK) </font><br>";
						else echo $subquery." <font color='#FF0000'> <------------Error----------</font><br>";
					}
					else
					{
						
						$result=mysql_query($subUpdateQuery);
						//echo $subUpdateQuery."<br>";
						if($result!=false)echo $subUpdateQuery." <font color='#0000FF'>(OK) </font><br>";
						else echo $subUpdateQuery." <font color='#FF0000'> <------------Error----------</font><br>";
					}
					echo "---------------------<br>";
					//$result = mysql_query($subquery);
					/*echo  $subquery."<br>";
					echo $subCheckQuery."<br>";
					echo $subUpdateQuery."<br>";
					echo "---------------------<br>";*/

				}
			}
		}
	}
	public function importMark($subjectID,$semeterID,$yearID)
	{
		////////////Create query header//////////////
		$table="KETQUA";
		$query = "INSERT INTO ".$table." VALUES (";
		$updateQuery = "UPDATE ".$table." SET ";
		$whereQuery =" where MAMONHOC = '$subjectID' and HOCKY = '$semeterID' and NAM = '$yearID' and ";
		$checkQuery = "SELECT COUNT(*) from ".$table." ";
//		foreach($fields as $str)
//		{
//			$query=$query.$str.",";
//		}
//		$query = substr($query,0,strlen($query)-1);
//		$query = $query.") VALUES (";
		///////////Separate Sheet////////////
		foreach($this->content as $sheet)
		{
			///////////Separate Row/////////////
			$i=1;
			foreach($sheet as $row)
			{
				/////////Eject Row 1 - Title//////////////
				if($i==1)
				{
					$i=2;
				}
				else
				{
					$subquery = $query;
					$subWhereQuery = $whereQuery;
					$subCheckQuery = "";
					$subUpdateQuery="";
					for($j=0;$j<count($row);$j++)
					{
						if($j==0)
						{
							$subWhereQuery = $subWhereQuery."MSSV = $row[$j]";
							$subquery = $subquery."$row[$j],'$subjectID',$semeterID,'$yearID',";
						}
						else
						{
							$subquery=$subquery."'".$row[$j]."',";
							if($j==1)
							{
								$subUpdateQuery = $subUpdateQuery."DIEMTHUCHANH = $row[$j],";
							}
							if($j==2)
							{
								$subUpdateQuery = $subUpdateQuery."DIEMGIUAKY = $row[$j],";
							}
							if($j==3)
							{
								$subUpdateQuery = $subUpdateQuery."DIEMCUOIKY = $row[$j],";
							}
							if($j==4)
							{
								$subUpdateQuery = $subUpdateQuery."DIEMTRUNGBINH = $row[$j] ";
							}
							
						}
					}
					$subCheckQuery=$checkQuery.$subWhereQuery;
					$subUpdateQuery= $updateQuery." ".$subUpdateQuery.$subWhereQuery;
					$subquery= substr($subquery,0,strlen($subquery)-1);
					$subquery=$subquery.")";
					$result = mysql_query($subCheckQuery);
					$result = mysql_fetch_array($result);
					if($result[0]=="0")
					{
						//echo $subquery."<br>";
						$result=mysql_query($subquery);
						if($result!=false)echo $subquery." <font color='#0000FF'>(OK) </font><br>";
						else echo $subquery." <font color='#FF0000'> <------------Error----------</font><br>";
					}
					else
					{
						
						$result=mysql_query($subUpdateQuery);
						//echo $subUpdateQuery."<br>";
						if($result!=false)echo $subUpdateQuery." <font color='#0000FF'>(OK) </font><br>";
						else echo $subUpdateQuery." <font color='#FF0000'> <------------Error----------</font><br>";
					}
					//echo "---------------------<br>";
					//$result = mysql_query($subquery);
				//	echo  $subquery."<br>";
//					echo $subCheckQuery."<br>";
//					echo $subUpdateQuery."<br>";
//					echo "---------------------<br>";

				}
			}
		}
	}
}
?>